create  table jms_dm.dm_network_outbreak_detail_dt
(
virt_code   varchar(100) COMMENT '虚拟代理区code' ,
agent_code   varchar(100) COMMENT '代理区code' ,
code     varchar(100) COMMENT '网点code' ,
virt_name    varchar(100) COMMENT '虚拟代理区' ,
agent_name    varchar(100) COMMENT '代理区' ,
name     varchar(100) COMMENT '网点' ,
day_arrive_sum   int(12) COMMENT '当日到达量' ,
t_day_arrive_sum int(12) COMMENT '昨日到大量' ,
 arrive_rate   decimal(16,4) COMMENT '到件环比' ,
 day_sign_sum    int(12) COMMENT '当日签收量' ,
t_day_sign_sum int(12) COMMENT '昨日签收量' ,
sign_rate       decimal(16,4) COMMENT '签收比' ,
 day_deliver_sum   int(12) COMMENT '今日派件员数量' ,
t_day_deliver_sum int(12) COMMENT '昨日派件员数量' ,
deliver_rate   decimal(16,4) COMMENT '派件员环比' ,
retention_count_7d    int(12) COMMENT '七天滞留量' ,
digestion_ability     decimal(16,4) COMMENT '消化能力' ,
retention_count_1d    int(12) COMMENT '当日滞留量' ,
retention_1d_rate     decimal(16,4) COMMENT '当日滞留率' ,
2_1_sum    int(12) COMMENT '第二日滞留量' ,
retention_second_day_rate   decimal(16,4) COMMENT '第二日滞留量占比' ,
7_2_sum    int(12) COMMENT 't_2到T7滞留量' ,
retention_3_7_day_rate   decimal(16,4) COMMENT 't_2到T7滞留量占比',
date_time date COMMENT '业务日期'
)
ENGINE=OLAP
DUPLICATE KEY(virt_code,agent_code,`code`)
comment '网点爆仓明细正式表'
PARTITION BY RANGE (date_time) (
   START ("2023-01-30") END ("2023-03-31") EVERY (INTERVAL 1 day))DISTRIBUTED BY HASH(`code`) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);

alter table jms_dm.dm_network_outbreak_detail_dt add column (
end_provider_id varchar(100) comment'省份id',
end_provider_name varchar(100) comment '省份')  ;